
USE BDM
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE  [dbo].[BDM_LOAD] AS 

-- exec BDM_LOAD

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[code_land]') AND type in (N'U'))
DROP TABLE [dbo].[code_land]

CREATE TABLE [dbo].[code_land](
	[id_val] [int] IDENTITY(1,1) NOT NULL,
	[file_type] [varchar](2000) NULL,
	[file_location] [varchar](2000) NULL,
	[srv][varchar](2000) NULL,
	[code] [varchar] (max) NULL,
	[full_file] [varchar](2000) NULL,
	[import] [varchar](max) NULL,
	[duplicate_code] [int] NULL,
	[FileData] text ,
    [ImportDate] datetime 
) ON [PRIMARY]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cmd]') AND type in (N'U'))
DROP TABLE [dbo].[cmd]

CREATE TABLE [dbo].[cmd](
	[id_val] [int] IDENTITY(1,1) NOT NULL,
	[cmd] [varchar](2000) NULL
) ON [PRIMARY]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[codedupl]') AND type in (N'U'))
DROP TABLE [dbo].[codedupl]

CREATE TABLE [dbo].[codedupl](
	[code] varchar(max) NULL,
	[duplicate_code] [varchar](2000) NULL
) ON [PRIMARY]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[import]') AND type in (N'U'))
DROP TABLE [dbo].[import]
CREATE TABLE [dbo].[import](
	[import] [varchar](max) NULL
) ON [PRIMARY]

---- CREATE TEMPORARY OBJECTS  end

----> LOAD THE LOCATIONS OF ALL THE FILES
----> declare variables
declare @path varchar (2000)		-- path to folder with code
declare @type varchar (2000)		-- code types 
declare @max_file_type as int		-- number of code types
declare @max_file_location as int	-- number of folder paths to code
declare @file_type as int			-- counter for loop through file types
declare @file_location as int		-- counter for loop through file locations

----> set LOOP variables
set @file_type = (select Min(id_val) from file_type)				-- start file type loop at 1
set @max_file_type = (select MAX(id_val) from file_type)			-- set number of file types
set @file_location = (select Min(id_val) from file_location)		-- start file loation loop at 1 
set @max_file_location = (select MAX(id_val) from file_location)	-- set number of folders

print  @file_type
print @max_file_type
print @file_location
print @max_file_location


----> Loop through all file locations
	----> Within file locations loop through all file types
	----> For each file type load all the file names
		----> For each file name, load the file
WHILE @file_location <= @max_file_location												-- loop through file locations starting with 1
		BEGIN
		set @path = (select file_location from file_location where id_val = @file_location)		-- set file location to the number on list file location
			WHILE @file_type <= @max_file_type													-- loop through code types with the following
					BEGIN
					set @type = (select file_type from file_type where id_val = @file_type)				-- loop through file types starting with 1
					declare @cmd varchar(2000)	 
					set @cmd = 'dir /B ' + ' ' + cast(@path as varchar) + ' ' + cast(@type as varchar)
					print @cmd 
					insert into cmd exec master..xp_cmdshell  @cmd										-- load cmd table with file names in folder
					delete cmd where cmd is null or cmd like '%not found%'								-- delete from cmd all but current file type
					delete cmd where RIGHT(cmd,4) <> RIGHT(@type,4)
					----> start of loop that loads the code
						declare @cmdno as int
						declare @max_cmdno as int 
						set @cmdno = (select min(id_val) from cmd)										-- loop will start with lowest cmd id_val
						set @max_cmdno = (select max(id_val) from cmd)									-- loop will end with max cmd id_val
						WHILE @cmdno <= @max_cmdno
								BEGIN
									declare @cmd_val as varchar(2000)											-- declare current file name 
									set @cmd_val = (select cmd from cmd where id_val = @cmdno)					-- populate current file name 
									insert into code_land select @type,@path,'server', @cmd_val,@path+@cmd_val			-- populate code_land with type, path,file name, full location 
																		, null, null, null, null
									truncate table import														-- import is where we stage the full code
									set @cmdno = @cmdno +1						-- increment the file name by 1 
								END		
					set @file_type = @file_type + 1					-- increment the file type by 1 
					END
		set @file_type = (select Min(id_val) from file_type)-- reset file type counter			
		set @file_location = @file_location + 1				-- increment the file location by 1 
		END
----> LOAD THE CODE 
declare @cnow as int
set @cnow = (select min(id_val) from code_land)
declare @cmax as int
set @cmax = (select max(id_val) from code_land)
WHILE @cnow <= @cmax 
	BEGIN
	declare @imp as varchar (2000)
	declare @file as varchar (2000)
	print '@cnow'
	print @cnow
	truncate table import 
	set @file = (select full_file from code_land where id_val = @cnow)
		set      @imp =            'bulk insert'
		set      @imp = @imp +     ' Import' 
		set      @imp = @imp +     ' from'
		set      @imp = @imp +     ' ''' + @File + ''''
		set      @imp = @imp +     ' with (ROWTERMINATOR = ''/*gobbledygook*/'')'
	exec (@imp)
	print @imp
	declare @codebase as varchar(max)
	set @codebase = (select top 1 import from import)
	update code_land set import = @codebase where id_val = @cnow
	print '@codebase' 
	print @codebase 
	set @cnow = @cnow +1  
	END
insert into codedupl select code, count(*) as duplicate_code from code_land group by code 
update code_land set duplicate_code = b.duplicate_code from code_land a inner join codedupl b on a.code = b.code
update code_land set duplicate_code = (duplicate_code - 1 )
update code_land set filedata = import 
update code_land set ImportDate = GETDATE() 

